﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;

namespace PropertyManagement
{
    public class mydata
    {
        MySqlConnection conn;
        public void createConn()
        {
            string strcon = "Server=localhost;User ID=root;" +
              "Password=123456;DataBase=wygl;Charset=utf8;";

            conn = new MySqlConnection(strcon);
        }
        public void closeAll()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        // 1-取出 sqlcommand-使用命令参数
        public MySqlCommand getCommand(string strsql)
        {
            createConn();
            MySqlCommand cmd = new MySqlCommand(strsql, conn);

            return cmd;
        }
        //1- 执行command的 添加、更新、删除操作
        public int execNonQuery(MySqlCommand cmd)
        {
            int n = -1;
            try
            {
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                    n = cmd.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                cmd.Connection.Close();
                if (conn.State == ConnectionState.Open || conn != null)
                {
                    conn.Close();
                }
            }
            return n;
        }

        internal string getOnFieldFromTable(object strsql)
        {
            throw new NotImplementedException();
        }

        // 添加、更新、删除 2-字符串的格式
        public int execSql(string strsql)
        {
            int n;
            n = -1;
            try
            {
                if (conn == null)
                {
                    createConn();
                }
                else if (conn.State == ConnectionState.Closed)
                {
                    createConn();
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strsql, conn);
                n = cmd.ExecuteNonQuery();           // n=1,成功 -1错误             
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }

            return n;
        }

        // 得到数据集   
        public DataSet getDataSet(string strsql, string strTable)
        {
            DataSet ds = null;

            try
            {
                if (conn == null)
                {
                    createConn();
                }
                else if (conn.State == ConnectionState.Closed)
                {
                    createConn();
                }

                conn.Open();
                MySqlDataAdapter sda = new MySqlDataAdapter(strsql, conn);
                ds = new DataSet();
                sda.Fill(ds, strTable);

            }
            catch (MySqlException ex) { throw new Exception(ex.Message, ex); }
            finally
            {
                conn.Close();
            }
            return ds;
        }//end_dataset

        public MySqlDataReader getDataReader(string strsql)
        {
            MySqlDataReader dread = null;

            try
            {
                if (conn == null)
                {
                    createConn();
                }
                else if (conn.State == ConnectionState.Closed)
                {
                    createConn();
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strsql, conn);
                dread = cmd.ExecuteReader();

            }
            catch (MySqlException ex) { throw new Exception(ex.Message, ex); }

            return dread;
        }

        public int getTableIdByName(string strsql)
        {
            MySqlDataReader dread = null;
            int n1 = -1;
            try
            {
                if (conn == null)
                {
                    createConn();
                }
                else if (conn.State == ConnectionState.Closed)
                {
                    createConn();
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strsql, conn);
                dread = cmd.ExecuteReader();
                if (dread.HasRows)
                {
                    dread.Read();
                    n1 = Int16.Parse(dread["id"].ToString());
                }
                dread.Close();
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }
            return n1;
        }

        public int MultiExecSql(string[] arrsql)
        {
            int n1;
            n1 = -1;
            try
            {
                if (conn == null)
                {
                    createConn();
                }
                conn.Open();

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                for (int i = 0; i < arrsql.Length; i++)
                {
                    string strsql = arrsql[i];
                    if (strsql != null && strsql.Length > 6)
                    {
                        cmd.CommandText = strsql;
                        n1 = cmd.ExecuteNonQuery();
                        if (n1 == -1)
                        {
                            break;
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }
            return n1;
        }

        public bool isName(string strsql)
        {
            bool flag = false;
            try
            {
                if (conn == null)
                {
                    createConn();
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strsql, conn);
                if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
                {

                }
                flag = true;
            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                conn.Close();
            }
            return flag;
        }

        public string getOnFieldFromTable(string strsql)
        {
            object sname;
            try
            {
                if (conn == null)
                {
                    createConn();
                }
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(strsql, conn);
                sname = cmd.ExecuteReader();

            }
            catch (MySqlException ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {

                conn.Close();
            }
            return sname == null ? string.Empty : sname.ToString();
        }

    }
}
